#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
快速数据库导出工具：一键导出所有数据库内容到Excel
优化版本：解决信息显示不全问题
"""

import sqlite3
import json
import os
from datetime import datetime
import pandas as pd
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter

def quick_export():
    """快速导出所有数据库内容"""
    print("=" * 60)
    print("快速数据库导出工具 - 优化版")
    print("=" * 60)
    
    db_path = 'data/app.db'
    export_dir = 'data/exports'
    
    # 确保导出目录存在
    os.makedirs(export_dir, exist_ok=True)
    
    if not os.path.exists(db_path):
        print("❌ 数据库文件不存在: data/app.db")
        return
    
    try:
        with sqlite3.connect(db_path) as conn:
            # 生成文件名
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f'数据库导出_{timestamp}.xlsx'
            filepath = os.path.join(export_dir, filename)
            
            print(f"正在导出数据库到: {filepath}")
            
            with pd.ExcelWriter(filepath, engine='openpyxl') as writer:
                # 1. 用户数据表
                print("  - 导出用户数据...")
                users_query = '''
                    SELECT 
                        student_id as '学号',
                        name as '姓名',
                        password_hash as '密码哈希',
                        salt as '密码盐值',
                        college as '学院',
                        major as '专业',
                        phone as '电话',
                        grade as '年级',
                        profile as '个人简介',
                        avatar_url as '头像URL',
                        resume_data as '简历数据',
                        unread_news as '未读消息',
                        created_at as '注册时间',
                        last_login as '最后登录'
                    FROM users
                    ORDER BY created_at DESC
                '''
                users_df = pd.read_sql_query(users_query, conn)
                users_df.to_excel(writer, sheet_name='用户数据', index=False)
                
                # 2. 对话概览表
                print("  - 导出对话概览...")
                conversations_query = '''
                    SELECT 
                        c.id as '对话ID',
                        c.student_id as '用户学号',
                        u.name as '用户姓名',
                        c.title as '对话标题',
                        c.created_at as '创建时间',
                        c.updated_at as '更新时间',
                        COUNT(m.id) as '消息数量'
                    FROM chat_conversations c
                    LEFT JOIN users u ON c.student_id = u.student_id
                    LEFT JOIN chat_messages m ON c.id = m.conversation_id
                    GROUP BY c.id, c.student_id, u.name, c.title, c.created_at, c.updated_at
                    ORDER BY c.updated_at DESC
                '''
                conversations_df = pd.read_sql_query(conversations_query, conn)
                conversations_df.to_excel(writer, sheet_name='对话概览', index=False)
                
                # 3. 消息详情表
                print("  - 导出消息详情...")
                messages_query = '''
                    SELECT 
                        m.id as '消息ID',
                        m.conversation_id as '对话ID',
                        c.title as '对话标题',
                        u.name as '用户姓名',
                        m.type as '消息类型',
                        m.content as '消息内容',
                        m.timestamp as '发送时间'
                    FROM chat_messages m
                    LEFT JOIN chat_conversations c ON m.conversation_id = c.id
                    LEFT JOIN users u ON c.student_id = u.student_id
                    ORDER BY m.timestamp ASC
                '''
                messages_df = pd.read_sql_query(messages_query, conn)
                messages_df.to_excel(writer, sheet_name='消息详情', index=False)
                
                # 4. 竞赛信息表
                print("  - 导出竞赛信息...")
                competitions_query = '''
                    SELECT 
                        c.id as '竞赛ID',
                        c.name as '竞赛名称',
                        c.description as '竞赛描述',
                        c.status as '状态',
                        c.categories as '分类',
                        c.start_date as '开始日期',
                        c.end_date as '结束日期',
                        c.rating as '等级',
                        c.organizer as '主办方',
                        c.max_team_size as '最大团队人数',
                        c.url as '竞赛链接',
                        c.image_url as '图片链接',
                        c.detail as '详细信息',
                        c.created_at as '创建时间',
                        c.updated_at as '更新时间'
                    FROM competitions c
                    ORDER BY c.id ASC
                '''
                competitions_df = pd.read_sql_query(competitions_query, conn)
                competitions_df.to_excel(writer, sheet_name='竞赛信息', index=False)
                
                # 5. 竞赛分类表
                print("  - 导出竞赛分类...")
                categories_query = '''
                    SELECT 
                        cc.id as '分类ID',
                        cc.competition_id as '竞赛ID',
                        c.name as '竞赛名称',
                        cc.category as '分类名称'
                    FROM competition_categories cc
                    LEFT JOIN competitions c ON cc.competition_id = c.id
                    ORDER BY cc.competition_id ASC, cc.category ASC
                '''
                categories_df = pd.read_sql_query(categories_query, conn)
                categories_df.to_excel(writer, sheet_name='竞赛分类', index=False)
                
                # 6. 用户聊天汇总表
                print("  - 导出用户聊天汇总...")
                summary_query = '''
                    SELECT 
                        u.student_id as '学号',
                        u.name as '姓名',
                        u.college as '学院',
                        u.major as '专业',
                        u.grade as '年级',
                        COUNT(DISTINCT c.id) as '对话数量',
                        COUNT(m.id) as '消息总数',
                        SUM(CASE WHEN m.type = 'user' THEN 1 ELSE 0 END) as '用户消息数',
                        SUM(CASE WHEN m.type = 'ai' THEN 1 ELSE 0 END) as 'AI回复数',
                        MAX(c.updated_at) as '最后活跃时间',
                        MIN(c.created_at) as '首次对话时间'
                    FROM users u
                    LEFT JOIN chat_conversations c ON u.student_id = c.student_id
                    LEFT JOIN chat_messages m ON c.id = m.conversation_id
                    GROUP BY u.student_id, u.name, u.college, u.major, u.grade
                    ORDER BY COUNT(m.id) DESC
                '''
                summary_df = pd.read_sql_query(summary_query, conn)
                summary_df.to_excel(writer, sheet_name='用户聊天汇总', index=False)
                
                # 7. 数据统计表
                print("  - 生成数据统计...")
                stats_data = {
                    '统计项目': [
                        '总用户数',
                        '总对话数',
                        '总消息数',
                        '用户消息数',
                        'AI回复数',
                        '活跃用户数（有聊天记录）',
                        '平均每用户对话数',
                        '平均每对话消息数',
                        '总竞赛数',
                        '竞赛分类数',
                        '进行中的竞赛数',
                        '已结束的竞赛数'
                    ],
                    '数值': [
                        len(users_df),
                        len(conversations_df),
                        len(messages_df),
                        len(messages_df[messages_df['消息类型'] == 'user']),
                        len(messages_df[messages_df['消息类型'] == 'ai']),
                        len(summary_df[summary_df['消息总数'] > 0]),
                        round(len(conversations_df) / len(users_df), 2) if len(users_df) > 0 else 0,
                        round(len(messages_df) / len(conversations_df), 2) if len(conversations_df) > 0 else 0,
                        len(competitions_df),
                        len(categories_df),
                        len(competitions_df[competitions_df['状态'] == '进行中']),
                        len(competitions_df[competitions_df['状态'] == '已结束'])
                    ]
                }
                stats_df = pd.DataFrame(stats_data)
                stats_df.to_excel(writer, sheet_name='数据统计', index=False)
                
                # 格式化所有工作表
                print("  - 格式化工作表...")
                for sheet_name in writer.sheets:
                    worksheet = writer.sheets[sheet_name]
                    
                    # 设置标题行样式
                    header_font = Font(bold=True, color="FFFFFF")
                    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
                    header_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
                    
                    # 设置数据行样式
                    data_alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
                    
                    # 处理每一列
                    for col_num, column in enumerate(worksheet.columns, 1):
                        column_letter = get_column_letter(col_num)
                        max_length = 0
                        
                        # 计算最大列宽
                        for cell in column:
                            try:
                                cell_value = str(cell.value) if cell.value is not None else ""
                                # 计算显示宽度（中文字符按2个字符计算）
                                display_length = 0
                                for char in cell_value:
                                    if ord(char) > 127:  # 中文字符
                                        display_length += 2
                                    else:
                                        display_length += 1
                                max_length = max(max_length, display_length)
                            except:
                                pass
                        
                        # 设置列宽（最小10，最大100）
                        adjusted_width = max(10, min(max_length + 3, 100))
                        worksheet.column_dimensions[column_letter].width = adjusted_width
                        
                        # 设置标题行样式
                        header_cell = worksheet[f"{column_letter}1"]
                        header_cell.font = header_font
                        header_cell.fill = header_fill
                        header_cell.alignment = header_alignment
                        
                        # 设置数据行样式
                        for row_num in range(2, worksheet.max_row + 1):
                            cell = worksheet[f"{column_letter}{row_num}"]
                            cell.alignment = data_alignment
                            
                            # 对于长文本内容，设置自动换行
                            if cell.value and len(str(cell.value)) > 50:
                                cell.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
                    
                    # 冻结首行
                    worksheet.freeze_panes = "A2"
            
            print(f"\n✅ 导出完成！")
            print(f"文件位置: {filepath}")
            print(f"包含工作表:")
            print(f"  - 用户数据 ({len(users_df)} 条记录)")
            print(f"  - 对话概览 ({len(conversations_df)} 条记录)")
            print(f"  - 消息详情 ({len(messages_df)} 条记录)")
            print(f"  - 竞赛信息 ({len(competitions_df)} 条记录)")
            print(f"  - 竞赛分类 ({len(categories_df)} 条记录)")
            print(f"  - 用户聊天汇总 ({len(summary_df)} 条记录)")
            print(f"  - 数据统计")
            print(f"\n优化特性:")
            print(f"  - 自动调整列宽，支持中文字符")
            print(f"  - 长文本自动换行显示")
            print(f"  - 标题行高亮显示")
            print(f"  - 冻结首行便于查看")
            
            return filepath
            
    except Exception as e:
        print(f"❌ 导出失败: {e}")
        import traceback
        traceback.print_exc()
        return None

if __name__ == '__main__':
    quick_export()
